﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;


namespace BaseHelper.Data
{
    public class DBHelper
    {
        public OleDBHelper ole;
        public MsSqlHelper ms;
        public MySqlDBHelper my;
        public DBType SourceType;

        ~DBHelper()
        {
            this.Close();
            GC.SuppressFinalize(this);
        }

        public bool IsInit
        {
            get
            {
                if (SourceType == DBType.MSSQL && ms != null || SourceType == DBType.ACCESS && ole != null || SourceType == DBType.MYSQL && my != null)
                {
                    return true;
                }
                return false;
            }
        
        }

        public string GetLastSql
        {
            get {

                if (SourceType == DBType.MSSQL)
                    return ms.GetLastSql;
                else if (SourceType == DBType.MYSQL)
                    return my.GetLastSql;
                return "no sql!";
            }
        }

        public string Error
        {
            get {
                if (SourceType == DBType.MSSQL)
                    return ms.Error;
                else if (SourceType == DBType.ACCESS)
                    return ole.Error;
                else if (SourceType == DBType.MYSQL)
                    return my.Error;
                else
                    return "";
            }
        }

        public bool IsConntected
        {
            get
            {
                if (SourceType == DBType.MSSQL)
                    return ms!=null && ms.IsConntected;
                else if (SourceType == DBType.MYSQL)
                    return my!=null && my.IsConntected;

                return true;
            }
        }

        public bool Open(DBInfo db) 
        {
            SourceType = db.SourceType;
            if (SourceType == DBType.MSSQL)
            {
                ms = new MsSqlHelper(db);
                return ms.Open(db);
            }
            else if (SourceType == DBType.ACCESS)
            {
                ole = new OleDBHelper(db);
                return ole.Open(db);
            }
            else if (SourceType == DBType.MYSQL)
            {
                my = new MySqlDBHelper(db);
                return my.Open(db);
            }
            return false;
        }

        public void Init(DBInfo db,bool isKeepLive=false)
        {
            if (db == null)
                return;

            SourceType = db.SourceType;

            if (SourceType == DBType.MSSQL)
            {
                ms = new MsSqlHelper(db,isKeepLive);
            }
            else if (SourceType == DBType.ACCESS)
            {
                ole = new OleDBHelper(db);   
            }
            else if (SourceType == DBType.MYSQL)
            {
                my = new MySqlDBHelper(db, isKeepLive);
            }
        }

        public string Filter(string str)
        {
            //.Replace(" ","_").Replace("<","").Replace(">","").Replace("OR","").Replace("AND","").Replace("NOT","")
            return str.Replace("'", "’");
        }

        public string IntID(string str)
        {
            long ret;
            if (long.TryParse(str, out ret))
            {
                return ret.ToString();
            }
            return "";
        }

        public Hashtable[] ExecuteHashtable(string sql) 
        {

            if (SourceType == DBType.MSSQL && ms != null)
                return ms.ExecuteHashtable(sql);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.ExecuteHashtable(sql);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.ExecuteHashtable(sql);

            return null;
        }

        public bool IsDataReaderHasRow(string sql )
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.IsDataReaderHasRow(sql);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.IsDataReaderHasRow(sql);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.IsDataReaderHasRow(sql);
            return false;
        }

        public int NonQuery(string sql)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.NonQuery(sql);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.NonQuery(sql);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.NonQuery(sql);
            return -1;
        }

        public int NonQuery(string table,string where)
        {
            if (SourceType == DBType.MYSQL)
                return my.NonQuery(table, where);
            return -1;
        }

        public int Insert(string table,Hashtable ht,bool HasBinaryField=false)
        {
            if (SourceType == DBType.MSSQL && ms!=null)
                return ms.Insert(table, ht, HasBinaryField);
            else if (SourceType == DBType.ACCESS && ole!=null)
                return ole.Insert(table, ht);
            else if (SourceType == DBType.MYSQL &&  my != null)
                return my.Insert(table, ht,HasBinaryField);
        
            return -1;
        }

        public int Insert(string table, Hashtable[] hts)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Insert(table, hts);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Insert(table, hts);

            return -1;
        }

        public Hashtable[] Select(string table="", string where = "", string field = "*", string orderby = "",int page=-1,int pageSize=30)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Select(table,where,field,orderby,page,pageSize);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.Select(table, where, field, orderby, page, pageSize);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Select(table, where, field, orderby, page, pageSize);

            return null;
        }

        public bool IsSelectHasRow(string table, string where = "")
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.IsSelectHasRow(table,where );
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.IsSelectHasRow(table, where);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.IsSelectHasRow(table,where);

            return false;
        }


        public int Count(string table, string where = "")
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Count(table, where);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Count(table, where);

            return -1;
        }


        public int Update(string table, Hashtable ht, string where, bool HasBinaryField = false, bool isValue=false)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Update(table, ht, where, HasBinaryField, isValue);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.Update(table, ht, where);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Update(table, ht, where, HasBinaryField, isValue);

            return -1;
        }
        
        public int Delete(string table, string where="")
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Delete( table,  where);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.Delete(table, where);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Delete(table, where);
            
            return -1;
        }

        public string[] GetTables()
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.GetTables();
            else if (SourceType == DBType.MYSQL && my != null)
                return my.GetTables();

            return null;
        }

        public string[] GetFields(string table)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.GetFields(table);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.GetFields(table);

            return null;
        }

        public int Truncate(string tableName)
        {
            if (SourceType == DBType.MSSQL && ms != null)
                return ms.Truncate(tableName);
            else if (SourceType == DBType.ACCESS && ole != null)
                return ole.Truncate(tableName);
            else if (SourceType == DBType.MYSQL && my != null)
                return my.Truncate(tableName);
            
            return -1;
        }

        public void Close()
        {
            if (SourceType == DBType.MSSQL && ms != null)
            {
                ms.Close();
                ms = null;
            }
            else if (SourceType == DBType.MYSQL && my != null)
            {
                my.Close();
                my = null;
            }
        }

    }
}

